﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class TermCurrentDBAccess
    {
        /// <summary>
        /// Insert TermCurrent
        /// </summary>
        /// <param name="objTermCurrent"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public bool AddNewTermCurrent(TermCurrent objTermCurrent, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {                
                //new SqlParameter("@TermCurrentID", objTermCurrent.TermCurrentID),
                new SqlParameter("@Created", objTermCurrent.Created),
                new SqlParameter("@Updated",objTermCurrent.Updated),
                new SqlParameter("@Isactive", objTermCurrent.Isactive),
                new SqlParameter("@AccountNumber", objTermCurrent.AccountNumber),
                new SqlParameter("@UserID", objTermCurrent.UserID),
                new SqlParameter("@Term", objTermCurrent.Term),
                new SqlParameter("@Opendate", objTermCurrent.Opendate),
                new SqlParameter("@MaturityDate", objTermCurrent.MaturityDate),
                new SqlParameter("@Balance", objTermCurrent.Balance),
                new SqlParameter("@Currency", objTermCurrent.Currency),
                new SqlParameter("@InterestRateID", objTermCurrent.InterestRateID),
                new SqlParameter("@LocationID", objTermCurrent.LocationID),
                new SqlParameter("@Status", objTermCurrent.Status),
                new SqlParameter("@AccountHolders", objTermCurrent.AccountHolders),
                new SqlParameter("@Descriptions", objTermCurrent.Descriptions)
            };

            return SqlDBHelper.ExecuteNonQuery("AddNewTermCurrent", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
        }

        /// <summary>
        /// Update TermCurrent
        /// </summary>
        /// <param name="objTermCurrent"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public bool UpdateTermCurrent(TermCurrent objTermCurrent, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@TermCurrentID", objTermCurrent.TermCurrentID),
                new SqlParameter("@Created", objTermCurrent.Created),
                new SqlParameter("@Updated",objTermCurrent.Updated),
                new SqlParameter("@Isactive", objTermCurrent.Isactive),
                new SqlParameter("@AccountNumber", objTermCurrent.AccountNumber),
                new SqlParameter("@UserID", objTermCurrent.UserID),
                new SqlParameter("@Term", objTermCurrent.Term),
                new SqlParameter("@Opendate", objTermCurrent.Opendate),
                new SqlParameter("@MaturityDate", objTermCurrent.MaturityDate),
                new SqlParameter("@Balance", objTermCurrent.Balance),
                new SqlParameter("@Currency", objTermCurrent.Currency),
                new SqlParameter("@InterestRateID", objTermCurrent.InterestRateID),
                new SqlParameter("@LocationID", objTermCurrent.LocationID),
                new SqlParameter("@Status", objTermCurrent.Status),
                new SqlParameter("@Descriptions", objTermCurrent.Descriptions)
            };

            return SqlDBHelper.ExecuteNonQuery("UpdateTermCurrent", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
        }

        /// <summary>
        /// Delete TermCurrent
        /// </summary>
        /// <param name="TermCurrentID"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public bool DeleteTermCurrent(int TermCurrentID, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@TermCurrentID", TermCurrentID)
            };

            return SqlDBHelper.ExecuteNonQuery("DeleteTermCurrent", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
        }

        /// <summary>
        /// Get Term Current Details
        /// </summary>
        /// <param name="TermCurrentID"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public TermCurrent GetTermCurrentDetails(int TermCurrentID, String CONNECTION_STRING)
        {
            TermCurrent objTermCurrent = null;

            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@TermCurrentID",TermCurrentID),
            };
            //Lets get the list of all Term Current in a datataable
            using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetTermCurrentDetails", CommandType.StoredProcedure, CONNECTION_STRING, parameters))
            {
                //check if any record exist or not
                if (table.Rows.Count == 1)
                {
                    DataRow row = table.Rows[0];

                    //Lets go ahead and create the list of Non Term Current
                    objTermCurrent = new TermCurrent();

                    //Now lets populate  Term Current details into the list of Term Current                                          
                    objTermCurrent.TermCurrentID = Convert.ToInt32(row["TermCurrentID"]);
                    objTermCurrent.Created = row["Created"].ToString();
                    objTermCurrent.Updated = row["Updated"].ToString();
                    objTermCurrent.Isactive = row["Isactive"].ToString();
                    objTermCurrent.AccountNumber = row["AccountNumber"].ToString();
                    objTermCurrent.UserID = Convert.ToInt32(row["UserID"].ToString());
                    objTermCurrent.Term = Convert.ToInt32(row["Term"].ToString());
                    objTermCurrent.Opendate = row["Opendate"].ToString();
                    objTermCurrent.MaturityDate = row["MaturityDate"].ToString();
                    objTermCurrent.Balance = Convert.ToDouble(row["Balance"].ToString());
                    objTermCurrent.Currency = row["Currency"].ToString();
                    objTermCurrent.InterestRateID = Convert.ToInt32(row["InterestRateID"].ToString());
                    objTermCurrent.LocationID = Convert.ToInt32(row["LocationID"].ToString());
                    objTermCurrent.Status = row["Status"].ToString();
                    objTermCurrent.Descriptions = row["Descriptions"].ToString();
                }
            }

            return objTermCurrent;
        }


        /// <summary>
        /// Get Term Current MaturityDate
        /// </summary>
        /// <param name="AccountNumber"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public TermCurrent GetMaturityTermCurrent(string AccountNumber, String CONNECTION_STRING)
        {
            TermCurrent objTermCurrent = null;

            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@AccountNumber",AccountNumber),
            };
            //Lets get the list of all Term Current in a datataable
            using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetMaturityTermCurrent", CommandType.StoredProcedure, CONNECTION_STRING, parameters))
            {
                //check if any record exist or not
                if (table.Rows.Count == 1)
                {
                    DataRow row = table.Rows[0];

                    //Lets go ahead and create the list of Non Term Current
                    objTermCurrent = new TermCurrent();

                    //Now lets populate  Term Current details into the list of Term Current                                          
                    //objTermCurrent.TermCurrentID = Convert.ToInt32(row["TermCurrentID"]);
                    //objTermCurrent.Created = row["Created"].ToString();
                    //objTermCurrent.Updated = row["Updated"].ToString();
                    //objTermCurrent.Isactive = row["Isactive"].ToString();
                    //objTermCurrent.AccountNumber = row["AccountNumber"].ToString();
                    //objTermCurrent.UserID = Convert.ToInt32(row["UserID"].ToString());
                    //objTermCurrent.Term = Convert.ToInt32(row["Term"].ToString());
                    //objTermCurrent.Opendate = row["Opendate"].ToString();
                    objTermCurrent.MaturityDate = row["MaturityDate"].ToString();
                    //objTermCurrent.Balance = Convert.ToDouble(row["Balance"].ToString());
                    //objTermCurrent.Currency = row["Currency"].ToString();
                    //objTermCurrent.InterestRateID = Convert.ToInt32(row["InterestRateID"].ToString());
                    //objTermCurrent.LocationID = Convert.ToInt32(row["LocationID"].ToString());
                    //objTermCurrent.Status = row["Status"].ToString();
                    //objTermCurrent.Descriptions = row["Descriptions"].ToString();
                }
            }

            return objTermCurrent;
        }





        /// <summary>
        /// Get Term Current List
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public List<TermCurrent> GetTermCurrentList(int UserId, String CONNECTION_STRING)
        {
            List<TermCurrent> listTermCurrent = null;
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@UserId",UserId),
            };
            //Lets get the list of all Term Current in a datataable
            using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetTermCurrentList", CommandType.StoredProcedure, CONNECTION_STRING, parameters))
            {
                //check if any record exist or not
                if (table.Rows.Count > 0)
                {
                    //Lets go ahead and create the list of Term Current
                    listTermCurrent = new List<TermCurrent>();

                    //Now lets populate Term Current details into the list of Term Current
                    foreach (DataRow row in table.Rows)
                    {
                        TermCurrent objTermCurrent = new TermCurrent();
                        objTermCurrent.TermCurrentID = Convert.ToInt32(row["TermCurrentID"]);
                        objTermCurrent.Created = row["created"].ToString();
                        objTermCurrent.Updated = row["updated"].ToString();
                        objTermCurrent.Isactive = row["isactive"].ToString();
                        objTermCurrent.AccountNumber = row["AccountNumber"].ToString();
                        objTermCurrent.UserID = Convert.ToInt32(row["UserID"].ToString());
                        objTermCurrent.Term = Convert.ToInt32(row["term"].ToString());
                        objTermCurrent.Opendate = row["opendate"].ToString();
                        objTermCurrent.MaturityDate = row["MaturityDate"].ToString();
                        objTermCurrent.Balance = Convert.ToDouble(row["Balance"].ToString());
                        objTermCurrent.Currency = row["Currency"].ToString();
                        objTermCurrent.InterestRateID = Convert.ToInt32(row["InterestRateID"].ToString());
                        objTermCurrent.LocationID = Convert.ToInt32(row["LocationID"].ToString());
                        objTermCurrent.Status = row["Status"].ToString();
                        objTermCurrent.Descriptions = row["Descriptions"].ToString();

                        listTermCurrent.Add(objTermCurrent);
                    }
                }
            }

            return listTermCurrent;
        }
        /// <summary>
        /// Get Term Current DataTable
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public DataTable GetTermCurrentTable(int UserID, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@UserID",UserID),
            };
            //Lets get the list of all Term Current in a datataable of User ID
            DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetTermCurrentTable", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
            return table;
        }

        /// <summary>
        /// Update Balance
        /// </summary>
        /// <param name="Balance"></param>
        /// <param name="AccountNumber"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>

        public bool UpdateTermCurrentBalanceReceived(String AccountNumber, Double Balance, String CONNECTION_STRING)
        {

            SqlParameter[] parameter = new SqlParameter[]
            {
                new SqlParameter("@AccountNumber",AccountNumber),
                new SqlParameter("@Balance", Balance),
            };
            return SqlDBHelper.ExecuteNonQuery("UpdateTermCurrentBalanceReceived", CommandType.StoredProcedure, CONNECTION_STRING, parameter);
        }

    }
}
